﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using SMTC.Model.TestCore;
using System.Data;
using System.Data.Common;
using IMPlant.Core.Data.Unity;
using SMTC.Model.Model;

namespace SMTC.Dao.Employee
{
    public class EmployeeQueryDao : BaseDao
    {
        #region SQL语句
        private const string SQL_SELECT_ALL_Employee = @"select UserID,EmpNbr,EmpName,EmpDep,Position,ExtNum,MTel,Email,Fax,Remark,UserName,UserPsw from SMT_UserInfo order by EmpNbr ";
        private const string SQL_For_SELECT_Employee = @"select UserID,EmpNbr,EmpName,EmpDep,Position,ExtNum,MTel,Email,Fax,Remark,UserName,UserPsw from SMT_UserInfo where EmpNbr like @EmpNbr and EmpName like @EmpName order by EmpNbr";
        private const string SQL_Check_Employee = @"select EmpNbr from SMT_UserInfo where EmpNbr=@EmpNbr";
        private const string SQL_Employee_For_Role = @"select UserID,EmpNbr,EmpName,EmpDep,Position,ExtNum,MTel,Email,Fax,Remark,UserName,UserPsw
                                                    from SMT_UserInfo where UserID NOT IN (select MemberID from dbo.SMT_MemberInRole where RoleID=@RoleID )";
        #endregion

        #region 查询方法
        /// <summary>
        /// 查询所有员工
        /// </summary>
        /// <returns></returns>
        public List<EmpInfo> SelectAllEmployeeGridViewBind()
        {
            List<EmpInfo> employeelist = DBTemplete.ExcuteGetList<EmpInfo>(CommandType.Text, SQL_SELECT_ALL_Employee);
            return employeelist;
        }
       /// <summary>
       /// 根据条件查询员工
       /// </summary>
       /// <param name="EMPNbr"></param>
       /// <param name="Name"></param>
       /// <returns></returns>
        public List<EmpInfo> ForSelectAllEmployeeGridViewBind(string EmpNbr,string Name)
        {
            DbParameter[] parameter =
            {
                DBparameterBuild.Build("@EmpNbr",EmpNbr)
               ,DBparameterBuild.Build("@EmpName",Name)
             };

            List<EmpInfo> forselectemployeelist = DBTemplete.ExcuteGetList<EmpInfo>(CommandType.Text, SQL_For_SELECT_Employee, parameter);
            return forselectemployeelist;
        
        }
        /// <summary>
        /// 插入前检查员工工号是否存在
        /// </summary>
        /// <param name="EMPNbr"></param>
        /// <param name="Name"></param>
        /// <returns></returns>
        /// 
        public bool ExistEmployee(string EmpNbr)
        {
            DbParameter[] parameter = 
            {
                DBparameterBuild.Build("@EmpNbr",EmpNbr)
            };

            if (Convert.ToString(DBTemplete.ExecuteScalar(CommandType.Text, SQL_Check_Employee, parameter))==EmpNbr)
            {
                return true;
            }
            else
            {
                return false;
            }
          
        
        
        }

        public List<EmpInfo> Employee_For_Role(string RoleID)
        {
            DbParameter[] parameter =
            {
                DBparameterBuild.Build("@RoleID",RoleID)

             };

            List<EmpInfo> employee_for_rolelist = DBTemplete.ExcuteGetList<EmpInfo>(CommandType.Text, SQL_Employee_For_Role, parameter);
            return employee_for_rolelist;

        }

        #endregion
    }
}